﻿using System;
using System.Data;
using System.Net.Mail;
using modele;

public class WODirectService
{
    private ConnectionBase sgbdr;
    public WODirectService()
    {
      sgbdr= new ConnectionBase();
    }
    public DataTable GetWorkOrders(string whereClause)
    {
        if (String.IsNullOrEmpty(whereClause)) whereClause = "WHERE TASKINWORKORDER.WORKORDERID = 0";
            
        string sql = "SELECT EMPLOYEE.EMPLOYEENAME, EMPLOYEE.EMPLOYEEID , EMPLOYEE.USERNAME, " 
				+ "CRITERIA.CRITERIA, convert(varchar, WORKORDER.DATEREQUEST, 101) as DATEREQUEST, WORKORDER.WORKORDERID, " 
				+ "WORKORDER.AUTHORISE, convert(varchar, WORKORDER.TIMEREQUEST, 108), "
				+ "TASKINWORKORDER.WORKORDERID, TASKINWORKORDER.PROBLEMID, "
				+ "TASKINWORKORDER.PROPOSENEWPROBLEM, convert(varchar, TASKINWORKORDER.DATEREQUIRED, 101) as DATEREQUIRED, "
				
				+ "REQUESTMADEBY.REQUEST, REQUESTMADEBY.REQUESTMADEBYID "
				+ "FROM WORKORDER INNER JOIN " 
				+ "TASKINWORKORDER ON WORKORDER.WORKORDERID = TASKINWORKORDER.WORKORDERID INNER JOIN "
				
				+ "REQUESTMADEBY ON WORKORDER.REQUESTMADEBYID = REQUESTMADEBY.REQUESTMADEBYID INNER JOIN "
				+ "EMPLOYEE ON WORKORDER.EMPLOYEEID = EMPLOYEE.EMPLOYEEID INNER JOIN "
				+ "CRITERIA ON WORKORDER.CRITERIAID = CRITERIA.CRITERIAID " + whereClause;

        DataTable ds = new DataTable();
        ds  = sgbdr.consultSQL(sql).Tables[0];
        return ds;
        
    }
    public DataTable GetWorkOrderTasks (int workOrderId)
    {
        string whereClause = workOrderId  == 0
                                 ?
                                     " WHERE TASKINWORKORDER.WORKORDERID = -1"
                                 : " WHERE TASKINWORKORDER.WORKORDERID = " + workOrderId;

        
            string sql = "SELECT DISTINCT TASKINWORKORDER.TASKINWORKORDERID,TASKINWORKORDER.PROPOSENEWPROBLEM, EQUIPEMENT.CODENUMBER, EQUIPEMENT.DESCRIPTIONEQUIPMENT, TASKINWORKORDER.WORKORDERID, "
                         +
                         " convert(varchar, TASKINWORKORDER.DATEREQUIRED, 101) as DATEREQUIRED, TASKINWORKORDER.PROBLEMID, "
                         + " PROBLEM.PROBLEMID, PROBLEM.PROBLEM, HAVE.typeEquipementID, HAVE.systemID, "
                         +
                         " BUILDING.buildingID, BUILDING.building, SYSTEM.systemID, SYSTEM.system,location.location, own.locationid "
                         + " FROM TASKINWORKORDER INNER JOIN "
                         + " PROBLEM ON TASKINWORKORDER.PROBLEMID = PROBLEM.PROBLEMID INNER JOIN "
                         + " GROUPEPROBLEM ON GROUPEPROBLEM.GROUPEPROBLEMID = PROBLEM.GROUPEPROBLEMID INNER JOIN "
                         +
                         " TYPEEQUIPMENT ON GROUPEPROBLEM.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN "
                         +" EQUIPEMENT ON TASKINWORKORDER.CODENUMBER = EQUIPEMENT.CODENUMBER INNER JOIN "
                         + 
                         "EQUIPMENTPURCHASEINFORMATION ON EQUIPMENTPURCHASEINFORMATION.PURCHASEID = EQUIPEMENT.PURCHASEID INNER JOIN "
                         + "HAVE ON HAVE.haveID = EQUIPMENTPURCHASEINFORMATION.haveID INNER JOIN "
                         + "SYSTEM ON SYSTEM.SYSTEMID = HAVE.systemID INNER JOIN "
                         + "OWN ON EQUIPEMENT.CODENUMBER = OWN.CODENUMBER INNER JOIN "
                         + "BUILDING ON OWN.buildingID = BUILDING.buildingID inner join "
                         + "location on location.locationid = own.locationid "
                         + whereClause + " and Taskinworkorder.cancel=0";


        DataTable ds = new DataTable();
        ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;
    }
    public DataTable IsCodeNumberExist(string CodeNumber)
    {
        string sql = "SELECT HAVE.HAVEID, HAVE.typeEquipementID ,HAVE.systemID, equipement.modelid " +
                     "FROM EQUIPEMENT INNER JOIN EQUIPMENTPURCHASEINFORMATION ON EQUIPMENTPURCHASEINFORMATION.PURCHASEID = EQUIPEMENT.PURCHASEID  " +
                     "INNER JOIN HAVE ON HAVE.HAVEID = EQUIPMENTPURCHASEINFORMATION.HAVEID INNER JOIN MODEL ON MODEL.MODELID=EQUIPEMENT.MODELID " +
                     "WHERE (EQUIPEMENT.CODENUMBER ='"+ CodeNumber+"')";

        DataTable ds = new DataTable();
        ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;
    }
    public void sendConfirmWorkorder(int idWorkorder, string codeNumber, string problem, string _ITLocation)
    {
        string getEmployeeEmailSql = "SELECT convert(varchar,DATEREQUIRED,101) as DATEREQUIRED,REQUEST,CRITERIA,dbo.EMPLOYEE.EMPLOYEEID, dbo.EMPLOYEE.EMPLOYEENAME, dbo.EMPLOYEE.EMPLOYEEEMAILADRESS,CONVERT(VARCHAR,WORKORDER.DATEREQUEST,101) AS DATEREQUEST "
            + " FROM WORKORDER INNER JOIN EMPLOYEE ON EMPLOYEE.EMPLOYEEID = WORKORDER.EMPLOYEEID INNER JOIN"
            + " CRITERIA ON CRITERIA.CRITERIAID = WORKORDER.CRITERIAID INNER JOIN"
            + " REQUESTMADEBY ON REQUESTMADEBY.REQUESTMADEBYID = WORKORDER.REQUESTMADEBYID INNER JOIN"
            + " TASKINWORKORDER ON TASKINWORKORDER.WORKORDERID = WORKORDER.WORKORDERID"
            + " WHERE(dbo.workorder.workorderID =" + idWorkorder + ")";

        DataSet requesterGet = sgbdr.consultSQL(getEmployeeEmailSql);
        
        string criteria = requesterGet.Tables[0].Rows[0]["CRITERIA"].ToString();
        string requiredDate = requesterGet.Tables[0].Rows[0]["DATEREQUIRED"].ToString();

        string sqlSystem = "SELECT dbo.SYSTEM.SYSTEM, dbo.EQUIPEMENT.CODENUMBER, dbo.MODEL.MODELID, MODEL.MODEL, dbo.TYPEEQUIPMENT.TYPEEQUIPEMENTID, typeequipment.typeequipment, EMPLOYEE.EMPLOYEEID, Employee.EmployeeName, EMPLOYEE.EMPLOYEEEMAILADRESS"
            + " FROM TYPEEQUIPMENT INNER JOIN"
            + " HAVE ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN"
            + " SYSTEM ON HAVE.systemID = SYSTEM.SYSTEMID INNER JOIN"
            + " [IN] ON [IN].typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN"
            + " MODEL ON MODEL.MODELID = [IN].modelID INNER JOIN"
            + " EQUIPEMENT ON EQUIPEMENT.MODELID = MODEL.MODELID INNER JOIN"
            + " EQUIPMENTPURCHASEINFORMATION ON EQUIPMENTPURCHASEINFORMATION.PURCHASEID = EQUIPEMENT.PURCHASEID AND"
            + " EQUIPMENTPURCHASEINFORMATION.haveID = HAVE.haveID inner join own on own.codenumber = equipement.codenumber"
            + " iNNER JOIN EMPLOYEE ON EMPLOYEE.EMPLOYEEID = OWN.EMPLOYEEID AND OWN.UNUSE = 0"
            + " WHERE (dbo.EQUIPEMENT.CODENUMBER = " + codeNumber + ")";
        DataSet ownerGet = sgbdr.consultSQL(sqlSystem);

        SendMail mail = new SendMail();
        //sender//            
        mail.From = new MailAddress(_ITLocation);//ds.Tables[0].Rows[0]["EMPLOYEEEMAILADRESS"].ToString();                        			//*			
        //to requester//
        if (Convert.ToInt16(requesterGet.Tables[0].Rows[0]["EMPLOYEEID"]) != Convert.ToInt16(ownerGet.Tables[0].Rows[0]["EMPLOYEEID"]))
        {
            if (requesterGet.Tables[0].Rows[0]["employeeEmailAdress"].ToString().Length > 0)
                mail.To = new MailAddress(requesterGet.Tables[0].Rows[0]["employeeEmailAdress"].ToString() + ",");
            //to owner(user)//
            if (ownerGet.Tables[0].Rows[0]["employeeEmailAdress"].ToString().Length > 0)
                mail.To = new MailAddress("chantha.kd@gmail.com");
                    //new MailAddress(mail.To.ToString()+ownerGet.Tables[0].Rows[0]["employeeEmailAdress"].ToString());
        }
        else
        {
            if (requesterGet.Tables[0].Rows[0]["employeeEmailAdress"].ToString().Length > 0)
                mail.To = new MailAddress("chantha.kd@gmail.com");
                    //new MailAddress(requesterGet.Tables[0].Rows[0]["employeeEmailAdress"].ToString());
        }
        //reciever copy to//
        mail.Cc = new MailAddress(_ITLocation);

        //*/mail.To = "srengty@yahoo.com";

        //subject of message//
        mail.Subject = "Intranet request IT Work Order: " + idWorkorder;


        //message body//

        mail.Body = "Thank you \"" + requesterGet.Tables[0].Rows[0]["EMPLOYEENAME"] + "\"";
        if (requesterGet.Tables[0].Rows[0]["employeeEmailAdress"].ToString().Length > 0)
            mail.Body += "<<" + requesterGet.Tables[0].Rows[0]["employeeEmailAdress"].ToString() + ">>";
        mail.Body += " for your submission.\n\n";
        mail.Body += "mail message has been automatically generated in response\n";
        mail.Body += "to the submission IT Work Order, the content of which appears below:\n\n\n";
        mail.Body += "Description     : " + problem + "\n";
        mail.Body += "Criteria        : " + criteria + "\n";
        mail.Body += "Required Date   : " + requiredDate + "\n";
        //mail.Body += "Priority        : "+dataGet.Tables[0].Rows[0]["Priority"]+"\n";
        mail.Body += "Equipment Code  : " + codeNumber + "\n";
        mail.Body += "System Name     : " + ownerGet.Tables[0].Rows[0]["SYSTEM"] + "\n";
        mail.Body += "User Name       : " + ownerGet.Tables[0].Rows[0]["employeename"] + "\n\n";
        //mail.Body += "Follow your request dated '"+Email.Tables[0].Rows[0]["DATEREQUEST"]+"'\n";
        mail.Body += "IT support has assigned the IT Work Order No. " + idWorkorder + " to mail matter,\n"
            + "and we will respond to your query as soon as possible.\n\n";

        mail.Body += "In all future correspondence about mail particular matter, please "
            + "Ensure that the following string is included in the subject of your \nmessage:\n\n"
            + idWorkorder + "\n\n\n";

        mail.Body += "In future correspondence with IT support about any other matter, please\n";
        mail.Body += "Submit an IT Work Order, so that a new Work Order can be generate for\n";
        mail.Body += "your request.\n\n";
        mail.Body += "By following mail directions, your correspondence with IT Support will\n"
            + "be correctly tracked in our IT database, resulting in faster and more\nreliable response to your request.\n\n"
            + "Your request will be processed by our team during working hours:\n\n"
            + "- Monday to Friday 08:00 - 18:00\n"
            + "- Saturday         08:00 - 12:00\n"
            + "THERE IS NO NEED TO REPLY TO mail MESSAGE,\n\n"
            + "Thanks and best regards,\nIT Support";

        mail.Body += mail.Signature;
        
			
        //  172.17.28.51


	    SmtpClient smtpClient = new SmtpClient("91.121.106.81");
        
        smtpClient.Send(mail.From.ToString(),mail.To.ToString(),mail.Cc.ToString(),mail.Body);
       
		
			
       
    }
    public DataTable GetPlannings(string whereClause)
    {
        if (String.IsNullOrEmpty(whereClause))
            whereClause = " AND PLANNINGID = 0 "; 
        string sql = "SELECT PLANNING.PLANNINGID, PLANNING.TASKINWORKORDERID,TASKINWORKORDER.CODENUMBER,TASKINWORKORDER.PROPOSENEWPROBLEM ,CONVERT(varchar, PLANNING.PLANDATE, 101) AS PLANDATE, CONVERT(varchar, PLANNING.PLANTIME, 8) AS PLANTIME, PLANNING.PRIORITY, PLANNING.ESTIMATETIMEINMINUTE, "
                         + "PLANNING.REMARK, EMPLOYEE.EMPLOYEENAME, TASKINWORKORDER.CODENUMBER, TASKINWORKORDER.PROPOSENEWPROBLEM, TASKINWORKORDER.DATEREQUIRED, TASKINWORKORDER.WORKORDERID, LOCATION.LOCATION FROM PLANNING INNER JOIN EMPLOYEE "
                         + "ON PLANNING.EMPLOYEEID = EMPLOYEE.EMPLOYEEID INNER JOIN TASKINWORKORDER ON PLANNING.TASKINWORKORDERID = TASKINWORKORDER.TASKINWORKORDERID AND TASKINWORKORDER.cancel = 0 INNER JOIN WORKORDER ON TASKINWORKORDER.WORKORDERID = WORKORDER.WORKORDERID "
                         + "INNER JOIN OWN ON TASKINWORKORDER.CODENUMBER = OWN.CODENUMBER INNER JOIN LOCATION ON OWN.LOCATIONID = LOCATION.LOCATIONID  WHERE (OWN.unuse = 0) AND (TASKINWORKORDER.TASKSTATUSID<>4) "+ whereClause +" ORDER BY TASKINWORKORDER.WORKORDERID";
        DataTable ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;
    }

    public DataTable GetAvailability(string whereClause)
    {

        string sql = " select  planningId,workOrder.workorderid,criteria.criteria,employee.EmployeeName,Problem.Problem,plantime,estimateTimeInMinute from planning " +
                     " inner join employee on employee.EmployeeId  = planning.employeeId " +
                     " inner join taskinworkoRder on taskinworkoRder.taskInworkorderid =planning.taskInworkorderid "+
                     " inner join problem on taskinworkoRder.problemiD  = problem.problemiD "+
                     " inner join workOrder on workorder.workOrderId  = taskinworkoRder.workOrderId " + 	
		             " inner join criteria on workorder.criteriaId  = criteria.criteriaId "+
                     whereClause +
                     " ORDER BY PLANTIME ";
        DataTable ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;
    }
    public DataTable GetPreventiveMaintenance(string whereClause)
    {
        string sql = "select distinct MAINTENANCEID,EMPLOYEENAME,TYPEEQUIPMENT,NUMBEROFMONTHS,CONVERT(varchar, STARTDATE, 101) AS startdate,building.building from PREVENTIVEMAINTENANCE,employee,typeequipment,building,HAVE where(employee.employeeID=PREVENTIVEMAINTENANCE.employeeID and typeequipment.typeequipementid=HAVE.TYPEEQUIPEMENTID AND HAVE.HAVEID=PREVENTIVEMAINTENANCE.HAVEid and building.buildingid = PREVENTIVEMAINTENANCE.buildingid " + whereClause + " ) order By MAINTENANCEID";
        DataTable ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;
    }
    public DataTable GetSystemAndEquipmentType(int haveiD)
    {
        string sql = " select HAVE.SYSTEMID,SYSTEM.System, typeequipment.typeequipment ,HAVE.TYPEEQUIPEMENTID "+ 
                     " from HAVE inner join [System] on  [System].SystemId = have.SystemId "+
                     " inner join typeequipment on have.typeequipementid  = typeequipment.typeequipementid where HAVEID = " + haveiD + "";
        DataTable ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;

    }
   
   

}
